﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using HZ.Public;
using MySql.Data.MySqlClient;

namespace HZ.PublicDispatch
{
    public class CallMySqlClass
    {
        //数据库连接测试
        public static string sql_ip = "127.0.0.1";  //IP
        public static string sql_port = "3306"; //端口号
        public static string sql_user = "root";  //用户名  
        public static string sql_pw = "root";  //密码
        public static string sql_name = "catlrgv2"; //数据库名
        public static string sql_con_state = "off";  //连接状态
        public static MySqlConnection sql_connection;

        /// <summary>
        /// 连接数据库
        /// </summary>
        public static void MySqlCon()
        {
            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
            builder.Server = sql_ip;
            builder.Port = (uint)PublicClass.ToInt(sql_port);
            builder.UserID = sql_user;
            builder.Password = sql_pw;
            builder.Database = sql_name;
            builder.Pooling = true;
            builder.CharacterSet = "utf8";

            //string ConnectionString = "server = " + sql_ip + "; port = " + (uint)PublicClass.ToInt(sql_port) + "; userid = " + sql_user + "; password = " + sql_pw + "; database =" + sql_name + "; pooling=true; Charset=utf8;";                                                                                                                                                                                                                                     // string ConnectionString = "server = " + this.sql_ip.Text + "; userid = "+this.sql_user.Text+"; password = "+ this.sql_pw.Text + "; database ="+ this.sql_name.Text + "; persistsecurityinfo = True;";
            //sql_connection = new MySqlConnection(ConnectionString);
            sql_connection = new MySqlConnection(builder.ConnectionString);
            try
            {
                sql_connection.Open();
                sql_con_state = "on";
            }
            catch (MySqlException ex)
            {
                sql_con_state = "off";
            }
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        public static void MySqlUnCon()
        {
            if (sql_con_state == "on")
            {
                sql_connection.Close();
                sql_con_state = "off";
            }
        }

        #region 增删改查测试
        /// <summary>
        /// 增
        /// </summary>
        public static int insert_data()
        {
            string table_name = "test_table";
            string sql = "insert into " + table_name + " (traycode,testtype) values ('test',0)";
            if (table_name == "" || sql == "")
            {
                return -1;
            }
            if (sql_con_state != "on")
            {
                return -2;
            }
            MySqlCommand cmd = new MySqlCommand(sql, sql_connection);
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 删
        /// </summary>
        public static int del_data()
        {
            string table_name = "test_table";
            string sql = "delete from " + table_name + " where traycode = 'test'";
            if (table_name == "" || sql == "")
            {
                return -1;
            }
            if (sql_con_state != "on")
            {
                return -2;
            }
            MySqlCommand cmd = new MySqlCommand(sql, sql_connection);
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 改
        /// </summary>
        public static int update_data()
        {
            string table_name = "test_table";
            string sql = "update " + table_name + " set testtype = 1 where traycode = 'test'";
            if (table_name == "" || sql == "")
            {
                return -1;
            }
            if (sql_con_state != "on")
            {
                return -2;
            }
            MySqlCommand cmd = new MySqlCommand(sql, sql_connection);
            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 查
        /// </summary>
        public static List<TestTableData> select_data()
        {
            List<TestTableData> list = new List<TestTableData>();
            //List<string> listtest = new List<string>();
            string table_name = "test_table";
            string sql = "select * from " + table_name;
            MySqlCommand cmd = new MySqlCommand(sql, sql_connection);//创建命令对象;
            MySqlDataReader reader = cmd.ExecuteReader();//执行命令,ExcuteReader返回的是DataReader对象
            while (reader.Read())
            {
                TestTableData testTableData = new TestTableData();
                testTableData.traycode = reader[0].ToString();
                testTableData.testtype = (int)reader[1];
                list.Add(testTableData);
                /*                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    listtest.Add(reader[i].ToString());//每一行的每一个数据作为string都写入list
                                }*/
            }
            return list;
        }

        public class TestTableData
        {
            public string traycode { get; set; }
            public int testtype { get; set; }

            public TestTableData()
            {
            }

            public TestTableData(string traycode, int testtype)
            {
                this.traycode = traycode;
                this.testtype = testtype;
            }
        }
        #endregion

        public class FormStatusTableData
        {
            public int pid { get; set; }
            public string mac_no { get; set; }
            public string ahead_id { get; set; }
            public string station_no { get; set; }
            public string back_id { get; set; }
            public string in_out_tray { get; set; }
            public string floor_no { get; set; }
            public string member_no { get; set; }
            public string channel_no { get; set; }
            public string tray_code { get; set; }
            public string floor_status { get; set; }
            public string get_put_req { get; set; }
            public string get_put_ans { get; set; }
            public string tray_seq_code { get; set; }
            public string battery_code { get; set; }
            public int bbattery_count { get; set; }
            public string battery_seq_code { get; set; }
            public string battery_status { get; set; }
            public DateTime relate_time { get; set; }
            public string start_mac { get; set; }
            public string start_answer { get; set; }
            public string manual_auto { get; set; }
            public string robot_status { get; set; }
            public string mac_alarm { get; set; }
            public string stop_mac { get; set; }
            public string stop_answer { get; set; }
            public string agv_arrive { get; set; }
            public string agv_code { get; set; }
            public string agv_rw_status { get; set; }
            public DateTime agv_modify_time { get; set; }
            public string mac_rw_status { get; set; }
            public DateTime mac_modify_time { get; set; }
            public string form_path { get; set; }
            public int form_result { get; set; }
            public string mac_online { get; set; }
            public string right_left { get; set; }
            public int mac_priority { get; set; }
            public DateTime calib_time { get; set; }
            public int mission_grade { get; set; }
            public int exe_frequency { get; set; }
            public string resource { get; set; }
            public string tier_no { get; set; }
            public int export_count { get; set; }
            public string export_path { get; set; }
            public int alarm { get; set; }
            public string Remarks { get; set; }
            public int available { get; set; }
            public int needle_bed_smoke { get; set; }
            public int door_induction { get; set; }
        }

        public class DataTableHelper<T> where T : class
        {
            /// <summary>  
            /// 用DataTable转换实体类
            /// </summary>  
            public static List<T> DataTableConvertToModel(DataTable dt)
            {
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;
                }
                List<T> modelList = new List<T>();
                foreach (DataRow dr in dt.Rows)//从该表的行的集合中循环取出一行数据
                {
                    T model = (T)Activator.CreateInstance(typeof(T));
                    for (int i = 0; i < dr.Table.Columns.Count; i++)
                    {
                        PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
                        if (propertyInfo != null && dr[i] != DBNull.Value)
                            propertyInfo.SetValue(model, Convert.ChangeType(dr[i], propertyInfo.PropertyType), null);
                    }
                    modelList.Add(model);
                }
                return modelList;
            }
        }

        /// <summary>
        /// 查全部FormStatusTable数据
        /// </summary>
        public static List<FormStatusTableData> select_FormStatusTableData()
        {
            List<FormStatusTableData> list = new List<FormStatusTableData>();
            DataTable dt = new DataTable();
            string table_name = "form_status_table";
            string sql = "select * from " + table_name;
            MySqlCommand cmd = new MySqlCommand(sql, sql_connection);//创建命令对象;
            MySqlDataAdapter mda = new MySqlDataAdapter(cmd); //获取结果集
            mda.Fill(dt);
            list = DataTableHelper<FormStatusTableData>.DataTableConvertToModel(dt);
            return list;
        }

        /// <summary>
        /// 通用改
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="content">修改内容</param>
        /// <param name="condition">条件</param>
        public static int update_data(string table_name, string content, string condition)
        {
            string sql = "update " + table_name + " set " + content + " where " + condition;
            if (table_name == "" || sql == "")
            {
                return -1;
            }
            if (sql_con_state != "on")
            {
                return -2;
            }
            MySqlCommand cmd = new MySqlCommand(sql, sql_connection);
            return cmd.ExecuteNonQuery();//受影响的行数
        }
    }
}
